Node / ReticulumProjects / MeshChatX.git / files / meshchatx / src / backend / database / messages.py

Displaying Raw • Download

Tff7b72import T7ee787json

Tff7b72from T7ee787datetime Tff7b72import Te6edf3UTCTb4b4b4, Te6edf3datetime

Tff7b72from T7ee787.T7ee787provider Tff7b72import Te6edf3DatabaseProvider

Tff7b72class T56d364MessageDAOTb4b4b4:

Tff7b72def Tff7b72__init__Tb4b4b4(Tff7b72selfTb4b4b4, Te6edf3providerTb4b4b4: Te6edf3DatabaseProviderTb4b4b4)Tb4b4b4:

Tff7b72selfTff7b72.Td2a8ffprovider Tff7b72= Te6edf3provider

Tff7b72def Td2a8ffupsert_lxmf_messageTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3dataTb4b4b4)Tb4b4b4:

T8b949e# Ensure data is a dict if it's a sqlite3.Row

Tff7b72if Tff7b72not Tffa657isinstanceTb4b4b4(Te6edf3dataTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:

Te6edf3data Tff7b72= Tffa657dictTb4b4b4(Te6edf3dataTb4b4b4)

T8b949e# Ensure all required fields are present and handle defaults

Te6edf3fields Tff7b72= Tb4b4b4[

Ta5d6ff"Ta5d6ffhashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffsource_hashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffdestination_hashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffstateTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffprogressTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffis_incomingTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffmethodTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffdelivery_attemptsTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffnext_delivery_attempt_atTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fftitleTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffcontentTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fffieldsTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fftimestampTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffrssiTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffsnrTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffqualityTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffis_spamTa5d6ff"Tb4b4b4,

Tb4b4b4]

Te6edf3columns Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3fieldsTb4b4b4)

Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3fieldsTb4b4b4)Tb4b4b4)

Te6edf3update_set Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6fffTa5d6ff"Tffd700{Te6edf3fTffd700}Ta5d6ff = EXCLUDED.Tffd700{Te6edf3fTffd700}Ta5d6ff" Tff7b72for Te6edf3f Tff7b72in Te6edf3fields Tff7b72if Te6edf3f Tff7b72!= Ta5d6ff"Ta5d6ffhashTa5d6ff"Tb4b4b4]Tb4b4b4)

Te6edf3query Tff7b72= Tb4b4b4(

Ta5d6fffTa5d6ff"Ta5d6ffINSERT INTO lxmf_messages (Tffd700{Te6edf3columnsTffd700}Ta5d6ff, created_at, updated_at) VALUES (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff, ?, ?) Ta5d6ff"

Ta5d6fffTa5d6ff"Ta5d6ffON CONFLICT(hash) DO UPDATE SET Tffd700{Te6edf3update_setTffd700}Ta5d6ff, updated_at = EXCLUDED.updated_atTa5d6ff"

Tb4b4b4)

Te6edf3params Tff7b72= Tb4b4b4[Tb4b4b4]

Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4:

Te6edf3val Tff7b72= Te6edf3dataTff7b72.Td2a8ffgetTb4b4b4(Te6edf3fTb4b4b4)

Tff7b72if Te6edf3f Tff7b72== Ta5d6ff"Ta5d6fffieldsTa5d6ff" Tff7b72and Tffa657isinstanceTb4b4b4(Te6edf3valTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:

Te6edf3val Tff7b72= Te6edf3jsonTff7b72.Td2a8ffdumpsTb4b4b4(Te6edf3valTb4b4b4)

Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3valTb4b4b4)

Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)

Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)

Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)

Tff7b72def Td2a8ffget_lxmf_message_by_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3message_hashTb4b4b4)Tb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3message_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffdelete_lxmf_message_by_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3message_hashTb4b4b4)Tb4b4b4:

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(

Ta5d6ff"Ta5d6ffDELETE FROM lxmf_messages WHERE hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3message_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffget_conversation_messagesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3limitTff7b72=T79c0ff100Tb4b4b4, Te6edf3offsetTff7b72=T79c0ff0Tb4b4b4)Tb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE destination_hash = ? OR source_hash = ? ORDER BY timestamp DESC LIMIT ? OFFSET ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3limitTb4b4b4, Te6edf3offsetTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffget_conversationsTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:

T8b949e# This is a bit complex in raw SQL, we need the latest message for each destination

Te6edf3query Tff7b72= Ta5d6ff"""

Ta5d6ff SELECT m1.* FROM lxmf_messages m1

Ta5d6ff JOIN (

Ta5d6ff SELECT

Ta5d6ff CASE WHEN is_incoming = 1 THEN source_hash ELSE destination_hash END as peer_hash,

Ta5d6ff MAX(timestamp) as max_ts

Ta5d6ff FROM lxmf_messages

Ta5d6ff GROUP BY peer_hash

Ta5d6ff ) m2 ON (CASE WHEN m1.is_incoming = 1 THEN m1.source_hash ELSE m1.destination_hash END = m2.peer_hash

Ta5d6ff AND m1.timestamp = m2.max_ts)

Ta5d6ff ORDER BY m1.timestamp DESC

Ta5d6ff Ta5d6ff"""

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Te6edf3queryTb4b4b4)

Tff7b72def Td2a8ffmark_conversation_as_readTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:

Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(

T8b949e"""

INSERT INTO lxmf_conversation_read_state (destination_hash, last_read_at, created_at, updated_at)

VALUES (?, ?, ?, ?)

ON CONFLICT(destination_hash) DO UPDATE SET

last_read_at = EXCLUDED.last_read_at,

updated_at = EXCLUDED.updated_at

"""Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffis_conversation_unreadTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:

Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

T8b949e"""

SELECT m.timestamp, r.last_read_at

FROM lxmf_messages m

LEFT JOIN lxmf_conversation_read_state r ON r.destination_hash = ?

WHERE (m.destination_hash = ? OR m.source_hash = ?)

ORDER BY m.timestamp DESC LIMIT 1

"""Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72if Tff7b72not Te6edf3rowTb4b4b4:

Tff7b72return Tff7b72False

Tff7b72if Tff7b72not Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_read_atTa5d6ff"Tb4b4b4]Tb4b4b4:

Tff7b72return Tff7b72True

Te6edf3last_read_at Tff7b72= Te6edf3datetimeTff7b72.Td2a8fffromisoformatTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_read_atTa5d6ff"Tb4b4b4]Tb4b4b4)

Tff7b72if Te6edf3last_read_atTff7b72.Td2a8fftzinfo Tff7b72is Tff7b72NoneTb4b4b4:

Te6edf3last_read_at Tff7b72= Te6edf3last_read_atTff7b72.Td2a8ffreplaceTb4b4b4(Te6edf3tzinfoTff7b72=Te6edf3UTCTb4b4b4)

Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fftimestampTa5d6ff"Tb4b4b4] Tff7b72> Te6edf3last_read_atTff7b72.Td2a8fftimestampTb4b4b4(Tb4b4b4)

Tff7b72def Td2a8ffmark_stuck_messages_as_failedTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(

T8b949e"""

UPDATE lxmf_messages

SET state = 'failed', updated_at = ?

WHERE state = 'outbound'

OR (state = 'sent' AND method = 'opportunistic')

OR state = 'sending'

"""Tb4b4b4,

Tb4b4b4(Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)Tb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffget_failed_messages_for_destinationTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE state = Ta5d6ff'Ta5d6fffailedTa5d6ff'Ta5d6ff AND destination_hash = ? ORDER BY id ASCTa5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffget_failed_messages_countTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:

Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT COUNT(*) as count FROM lxmf_messages WHERE state = Ta5d6ff'Ta5d6fffailedTa5d6ff'Ta5d6ff AND destination_hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72else T79c0ff0

T8b949e# Forwarding Mappings

Tff7b72def Td2a8ffget_forwarding_mappingTb4b4b4(

Tff7b72selfTb4b4b4,

Te6edf3alias_hashTff7b72=Tff7b72NoneTb4b4b4,

Te6edf3original_sender_hashTff7b72=Tff7b72NoneTb4b4b4,

Te6edf3final_recipient_hashTff7b72=Tff7b72NoneTb4b4b4,

Tb4b4b4)Tb4b4b4:

Tff7b72if Te6edf3alias_hashTb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappings WHERE alias_hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3alias_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72if Te6edf3original_sender_hash Tff7b72and Te6edf3final_recipient_hashTb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappings WHERE original_sender_hash = ? AND final_recipient_hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3original_sender_hashTb4b4b4, Te6edf3final_recipient_hashTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72return Tff7b72None

Tff7b72def Td2a8ffcreate_forwarding_mappingTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3dataTb4b4b4)Tb4b4b4:

T8b949e# Ensure data is a dict if it's a sqlite3.Row

Tff7b72if Tff7b72not Tffa657isinstanceTb4b4b4(Te6edf3dataTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:

Te6edf3data Tff7b72= Tffa657dictTb4b4b4(Te6edf3dataTb4b4b4)

Te6edf3fields Tff7b72= Tb4b4b4[

Ta5d6ff"Ta5d6ffalias_identity_private_keyTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6ffalias_hashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fforiginal_sender_hashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fffinal_recipient_hashTa5d6ff"Tb4b4b4,

Ta5d6ff"Ta5d6fforiginal_destination_hashTa5d6ff"Tb4b4b4,

Tb4b4b4]

Te6edf3columns Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3fieldsTb4b4b4)

Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3fieldsTb4b4b4)Tb4b4b4)

Te6edf3query Tff7b72= Ta5d6fffTa5d6ff"Ta5d6ffINSERT INTO lxmf_forwarding_mappings (Tffd700{Te6edf3columnsTffd700}Ta5d6ff, created_at) VALUES (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff, ?)Ta5d6ff" T8b949e# noqa: S608

Te6edf3params Tff7b72= Tb4b4b4[Te6edf3dataTff7b72.Td2a8ffgetTb4b4b4(Te6edf3fTb4b4b4) Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4]

Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)Tb4b4b4)

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)

Tff7b72def Td2a8ffget_all_forwarding_mappingsTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:

Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappingsTa5d6ff"Tb4b4b4)

Tff7b72def Td2a8ffmark_notification_as_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:

Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(

T8b949e"""

INSERT INTO notification_viewed_state (destination_hash, last_viewed_at, created_at, updated_at)

VALUES (?, ?, ?, ?)

ON CONFLICT(destination_hash) DO UPDATE SET

last_viewed_at = EXCLUDED.last_viewed_at,

updated_at = EXCLUDED.updated_at

"""Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffmark_all_notifications_as_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTb4b4b4)Tb4b4b4:

Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)

Tff7b72for Te6edf3destination_hash Tff7b72in Te6edf3destination_hashesTb4b4b4:

Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(

T8b949e"""

INSERT INTO notification_viewed_state (destination_hash, last_viewed_at, created_at, updated_at)

VALUES (?, ?, ?, ?)

ON CONFLICT(destination_hash) DO UPDATE SET

last_viewed_at = EXCLUDED.last_viewed_at,

updated_at = EXCLUDED.updated_at

"""Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72def Td2a8ffis_notification_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3message_timestampTb4b4b4)Tb4b4b4:

Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(

Ta5d6ff"Ta5d6ffSELECT last_viewed_at FROM notification_viewed_state WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,

Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,

Tb4b4b4)

Tff7b72if Tff7b72not Te6edf3row Tff7b72or Tff7b72not Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_viewed_atTa5d6ff"Tb4b4b4]Tb4b4b4:

Tff7b72return Tff7b72False

Te6edf3last_viewed_at Tff7b72= Te6edf3datetimeTff7b72.Td2a8fffromisoformatTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_viewed_atTa5d6ff"Tb4b4b4]Tb4b4b4)

Tff7b72if Te6edf3last_viewed_atTff7b72.Td2a8fftzinfo Tff7b72is Tff7b72NoneTb4b4b4:

Te6edf3last_viewed_at Tff7b72= Te6edf3last_viewed_atTff7b72.Td2a8ffreplaceTb4b4b4(Te6edf3tzinfoTff7b72=Te6edf3UTCTb4b4b4)

Tff7b72return Te6edf3message_timestamp Tff7b72<Tff7b72= Te6edf3last_viewed_atTff7b72.Td2a8fftimestampTb4b4b4(Tb4b4b4)